/*
 * csdra all rights received.
 */
package net.csdra.cms.wordpress.repository;

import com.fasterxml.jackson.databind.ObjectMapper;
import net.csdra.cms.wordpress.model.WpOptions;
import net.csdra.cms.wordpress.model.WpPosts;
import com.photobucket.jackson.dataformat.php.PhpFactory;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import javax.persistence.EntityManager;
import javax.persistence.NoResultException;
import javax.persistence.PersistenceContext;
import net.csdra.cms.api.model.Category;
import net.csdra.cms.api.model.Post;
import net.csdra.cms.wordpress.model.WpTerms;

/**
 *
 * @author Didy
 */
public class WordPressAdapter {

    @PersistenceContext
    EntityManager em;

    private String prepareSql(String sql, int blogId) {
        if (blogId == 1) {
            return sql;
        }
        sql = sql.replaceAll("wp_posts", "wp_" + blogId + "_posts");
        sql = sql.replaceAll("wp_commentmeta", "wp_" + blogId + "_commentmeta");
        sql = sql.replaceAll("wp_comments", "wp_" + blogId + "_comments");
        sql = sql.replaceAll("wp_links", "wp_" + blogId + "_links");
        sql = sql.replaceAll("wp_options", "wp_" + blogId + "_options");
        sql = sql.replaceAll("wp_postmeta", "wp_" + blogId + "_postmeta");
        sql = sql.replaceAll("wp_term_relationships", "wp_" + blogId + "_term_relationships");
        sql = sql.replaceAll("wp_term_taxonomy", "wp_" + blogId + "_term_taxonomy");
        sql = sql.replaceAll("wp_terms", "wp_" + blogId + "_terms");

        return sql;

    }

    public WpPosts getPost(int blogId, Long id) {

        String sql = prepareSql("select * from `wp_posts` p where p.ID=:id", blogId);

        return (WpPosts) em.createNativeQuery(sql, WpPosts.class)
                .setParameter("id", id)
                .getSingleResult();
    }

    public Collection<WpPosts> getPostsByCategory(int blogId, Long categoryId, int offset, int limit) throws IOException {
        String sql = prepareSql("select * from wp_posts p \n"
                + "	left join wp_term_relationships tr on tr.object_id=p.id \n"
                + "	left join wp_term_taxonomy tt on tt.term_taxonomy_id=tr.term_taxonomy_id \n"
                + "	left join wp_terms t on t.term_id=tt.term_id\n"
                + "	where tt.taxonomy=:taxonomy and tt.term_id=:termId"
                + "     and p.post_type='post' and p.post_status='publish' order by p.post_date desc", blogId);

        List resultList = em.createNativeQuery(sql, WpPosts.class)
                .setParameter("taxonomy", "category")
                .setParameter("termId", categoryId)
                .setFirstResult(offset)
                .setMaxResults(limit)
                .getResultList();

        Map<String, String> options = getOptions(blogId);
        String phpSource = options.get("sticky_posts");
        System.out.println("phpSource:" + phpSource);
        ObjectMapper mapper = new ObjectMapper(new PhpFactory());
        Map<Object, Long> readValue = mapper.readValue(phpSource, Map.class);
        List stickyPosts = new ArrayList();
        for (Long stickyId : readValue.values()) {
            WpPosts post = getPost(blogId, stickyId);
            if (resultList.contains(post)) {
                stickyPosts.add(post);
                resultList.remove(post);
            }
        }
        System.out.println("readValue:" + stickyPosts);

        stickyPosts.addAll(resultList);

        return stickyPosts;

    }

    public WpPosts getPage(int blogId, String name) {
        return (WpPosts) em.createNativeQuery(prepareSql("select * from wp_posts p where p.post_type='page' and p.post_status='publish' and p.post_name=:name", blogId), WpPosts.class)
                .setParameter("name", name)
                .getSingleResult();
    }

    public Collection<WpPosts> getSubpages(int blogId, Long id) {
        return em.createNativeQuery(prepareSql("select * from wp_posts p where p.post_type='page' and p.post_status='publish' and p.post_parent=:id order by p.menu_order", blogId), WpPosts.class)
                .setParameter("id", id)
                .getResultList();
    }

    public String getPostCover(int blogId, Long id) {
        try {
            return (String) em.createNativeQuery(prepareSql("SELECT p2.guid FROM wp_posts p left join wp_postmeta m on m.post_id=p.ID left join wp_posts p2 on p2.ID=m.meta_value where m.meta_key=\"_thumbnail_id\" and p.ID=:id", blogId))
                    .setParameter("id", id)
                    .setMaxResults(1)
                    .getSingleResult();
        } catch (NoResultException ex) {
            return null;
        }
    }

    public Map<String, String> getOptions(int blogId) {
        String sql = prepareSql("SELECT * FROM wp_options", blogId);
        List<WpOptions> resultList = em.createNativeQuery(sql, WpOptions.class).getResultList();
        Map<String, String> options = new HashMap();
        for (WpOptions option : resultList) {
            options.put(option.getName(), option.getValue());
        }

        return options;
    }

    public WpTerms getCategory(int blogId, Long id) {
        return em.createQuery(prepareSql("select c from WpTerms c where c.termId=:id", blogId), WpTerms.class)
                .setParameter("id", id)
                .setMaxResults(1)
                .getSingleResult();
    }

    public WpTerms getCategory(int blogId, Post post) {
        String sql = prepareSql("select wp_terms.* from wp_posts p \n"
                + "	left join wp_term_relationships tr on tr.object_id=p.id \n"
                + "	left join wp_term_taxonomy tt on tt.term_taxonomy_id=tr.term_taxonomy_id \n"
                + "	left join wp_terms t on t.term_id=tt.term_id\n"
                + "	where tt.taxonomy=:taxonomy and p.ID=:id"
                + "     and p.post_type='post' and p.post_status='publish' order by p.post_date desc", blogId);

        WpTerms result = (WpTerms) em.createNativeQuery(sql, WpTerms.class)
                .setParameter("taxonomy", "category")
                .setParameter("id", post.getId())
                .setMaxResults(1)
                .getSingleResult();

        return result;
    }

    public Collection<WpTerms> getCategories(int blogId, Long id) {
        String sql = prepareSql("SELECT t.* FROM wp_terms t left join wp_term_taxonomy tt on t.term_id = tt.term_id where tt.parent=:parentId", blogId);
        return em.createNativeQuery(sql, Category.class)
                .setParameter("parentId", id)
                .getResultList();
    }
}
